import json
from app.models import User, Role
from django.views import View
from django.http import JsonResponse
import pandas as pd
import os
from salary.settings.settings import BASE_DIR
import hashlib
import pytz
from django.utils import timezone


class UserList(View):
    def post(self, req):
        d = json.loads(req.body)
        da = list(User.objects.filter(phone=d["phone"]).values())
        da2 = list(User.objects.filter(user_ID=d["user_ID"]).values())
        da3 = list(User.objects.filter(email=d["email"]).values())
        if len(da) > 0:
            return JsonResponse({"code": 201, "message": "该手机号已存在"})
        elif len(da2) > 0:
            return JsonResponse({"code": 201, "message": "该身份证已存在"})
        elif len(da3) > 0:
            return JsonResponse({"code": 201, "message": "该邮箱已存在"})
        else:
            User.objects.create(number=d["number"], name=d["name"], sex=d["sex"], ID_type=d["ID_type"],
                                birth_data=d["birth_data"],
                                phone=d["phone"], password=d["password"], email=d["email"], education=d["education"],
                                user_ID=d["user_ID"], job=d["job"],
                                entry_time=d["entry_time"],
                                salary_start=d["salary_start"], regular_time=d["regular_time"],
                                bank_account=d["bank_account"],
                                bank_name=d["bank_name"], bank_ID=d["bank_ID"], social_state=d["social_state"],
                                social_cardinal=d["social_cardinal"], provide_insurance=d["provide_insurance"],
                                medical_insurance=d["medical_insurance"], basic_wage=d["basic_wage"],
                                unemployment_insurance=d["unemployment_insurance"],
                                accumulation=d["accumulation"], role_id=d["role"],
                                college_id=int(d["department_id"]))

            return JsonResponse({"code": 200, "message": "添加成功"})

    def get(self, req):
        page = int(req.GET.get("page"))
        pagesize = int(req.GET.get("pagesize"))
        keyword = req.GET.get("keyword", "")
        d_id = req.GET.get("department", "")
        start = (page - 1) * pagesize
        end = page * pagesize
        try:
            data = list(User.objects.filter(name__contains=keyword).values(
                'number', 'name', 'sex', 'ID_type', 'user_ID', 'birth_data', "phone", "password", "email", "education",
                "job", "entry_time", "salary_start", "regular_time", "bank_account", "bank_name",
                "bank_ID", "basic_wage", "dimission_time",
                "social_state", "social_cardinal", "provide_insurance", "medical_insurance", "unemployment_insurance",
                "accumulation", "role__role_name", "id", "college_id", "college__name"
            ))[start: end]
            c = User.objects.all().count()
            return JsonResponse({"code": 200, "message": "请求成功", "data": data, "total": c})
        except ValueError:
            data = list(User.objects.filter(name__contains=keyword, college_id=d_id).values(
                'number', 'name', 'sex', 'ID_type', 'user_ID', 'birth_data', "phone", "password", "email", "education",
                "job", "entry_time", "salary_start", "regular_time", "bank_account", "bank_name",
                "bank_ID", "college_id", "basic_wage", "dimission_time",
                "social_state", "social_cardinal", "provide_insurance", "medical_insurance", "unemployment_insurance",
                "accumulation", "role__role_name", "id", "college__name"
            ))[start: end]
            c = User.objects.all().count()
            return JsonResponse({"code": 200, "message": "请求成功", "data": data, "total": c})
        except:
            return JsonResponse({"code": 500, "message": "请求失败"})

    def delete(self, req):
        d = json.loads(req.body)
        User.objects.filter(id=d["id"]).delete()
        return JsonResponse({"code": 200, "message": "删除成功"})

    def put(self, req):
        d = json.loads(req.body)
        try:
            if d["flag"] == 0:
                User.objects.filter(id=d["id"]).update(name=d["name"], sex=d["sex"])
                lia = list(User.objects.filter(id=d["id"]).values('id', "name", "role__role_name", "phone", "sex",
                                                                  "password"))[0]
                return JsonResponse({"code": 200, "message": "信息修改成功", "data": lia})
            elif d["flag"] == 1:
                User.objects.filter(id=d["id"]).update(password=d["password"])
                lia = list(User.objects.filter(id=d["id"]).values('id', "name", "role__role_name", "phone", "sex",
                                                                  "password"))[0]
                return JsonResponse({"code": 200, "message": "密码修改成功", "data": lia})
        except KeyError:
            User.objects.filter(id=d["id"]).update(
                number=d["number"], name=d["name"], sex=d["sex"], ID_type=d["ID_type"],
                birth_data=d["birth_data"],
                phone=d["phone"], password=d["password"], email=d["email"], education=d["education"],
                user_ID=d["user_ID"], job=d["job"],
                entry_time=d["entry_time"],
                salary_start=d["salary_start"], regular_time=d["regular_time"],
                bank_account=d["bank_account"], basic_wage=d["basic_wage"],
                bank_name=d["bank_name"], bank_ID=d["bank_ID"], social_state=d["social_state"],
                social_cardinal=d["social_cardinal"], provide_insurance=d["provide_insurance"],
                medical_insurance=d["medical_insurance"],
                unemployment_insurance=d["unemployment_insurance"],
                accumulation=d["accumulation"], role_id=d["role"], collega_id=d["department_id"]
            )
            lia = list(User.objects.filter(id=d["id"]).values('id', "name", "role__role_name", "phone", "sex",
                                                              "password"))[0]
            return JsonResponse({"code": 200, "message": "请求成功", "data": lia})

    def patch(self, req):
        d = json.loads(req.body)
        tz = pytz.timezone('Asia/Shanghai')
        now_time = timezone.now().astimezone(tz=tz)
        current_date = now_time.strftime("%Y-%m-%d")
        User.objects.filter(id=d["id"]).update(user_state=d["user_state"], dimission_time=current_date)
        return JsonResponse({"code": 200, "message": "修改成功"})


# excel问题行
def add(li):
    aa = []
    for i in li:
        aa.append(i + 1)
    return aa


def excel(req):
    if req.method == 'POST':
        ex = req.FILES["file"]
        print(ex)
        f = open(os.path.join(BASE_DIR, '../static', f'{ex}'), "wb")
        for chunk in ex.chunks():
            f.write(chunk)
        f.close()
        aa = '123456'
        MD5 = hashlib.md5()
        MD5.update(aa.encode(encoding='utf-8'))
        file_name = f'static/{ex}'
        excels = pd.DataFrame(pd.read_excel(file_name, sheet_name="Sheet1"))
        rows = excels.shape[0]
        # 数据
        data = []
        for j in range(0, rows):
            data.append({
                "姓名": excels.loc[j]["姓名"],
                "性别": excels.loc[j]["性别"],
                "证件类型": excels.loc[j]["证件类型"],
                "证件号码": excels.loc[j]["证件号码"],
                "出生日期": excels.loc[j]["出生日期"],
                "手机号码": excels.loc[j]["手机号码"],
                "邮箱": excels.loc[j]["邮箱"],
                "学历": excels.loc[j]["学历"],
                "职位": excels.loc[j]["职位"],
                "入职时间": excels.loc[j]["入职时间"],
                "工资始发日期": excels.loc[j]["工资始发日期"],
                "转正日期": excels.loc[j]["转正日期"],
                "银行账户名称": excels.loc[j]["银行账户名称"],
                "银行名称": excels.loc[j]["银行名称"],
                "银行账号": excels.loc[j]["银行账号"],
                "社保状态": excels.loc[j]["社保状态"],
                "角色": excels.loc[j]["角色"],
                "学院": excels.loc[j]["学院"],
                "基本工资": excels.loc[j]["基本工资"],
                "工会费": excels.loc[j]["工会费"],
                "工资比率": excels.loc[j]["工资比率"],
            })
        # 手机号
        phone_list = []
        user_list = []
        email_list = []
        for i in data:
            da = list(User.objects.filter(phone=i["手机号码"]).values())
            da2 = list(User.objects.filter(user_ID=i["证件号码"]).values())
            da3 = list(User.objects.filter(email=i["邮箱"]).values())
            if len(da) > 0:
                dd = excels.loc[(excels["手机号码"] == i["手机号码"])]
                phone_list = dd.index
                continue
            elif len(da2) > 0:
                dd = excels.loc[(excels["证件号码"] == i["证件号码"])]
                user_list = dd.index
                continue
            elif len(da3) > 0:
                dd = excels.loc[(excels["邮箱"] == i["邮箱"])]
                email_list = dd.index
                continue
            else:
                print(i["角色"])
                roles = list(Role.objects.filter(role_name=i["角色"]).values())[0]

                User.objects.create(name=i["姓名"], sex=i["性别"], ID_type=i["证件类型"],
                                    birth_data=i["出生日期"],
                                    phone=i["手机号码"], email=i["邮箱"],
                                    education=i["学历"],
                                    user_ID=i["证件号码"], job=i["职位"],
                                    entry_time=i["入职时间"],
                                    salary_start=i["工资始发日期"], regular_time=i["转正日期"],
                                    bank_account=i["银行账户名称"],
                                    bank_name=i["银行名称"], bank_ID=i["银行账号"], social_state=i["社保状态"],
                                    password=MD5.hexdigest(),
                                    role_id=roles["id"], college_id=roles["department_id"], basic_wage=i["基本工资"],
                                    admission=i["工会费"], salarys=i["工资比率"])
        return JsonResponse({"code": 200, "message": "录入成功", "waring": {
            "phone": add(phone_list), "user_ID": add(user_list), "email": add(email_list)
        }})
